﻿Imports System.Data
Imports System.Data.Odbc

Module dataDepartamento




    Sub datosDepCBGrupoGastos()

        conectar()
        Dim dt3 As New DataTable

        cmd = New OdbcCommand("SELECT ID, Nombre FROM  tgruposdegastos", con)
        dr = cmd.ExecuteReader
        'executing the command and assigning it to connection 

        dt3.Load(dr, LoadOption.OverwriteChanges)
        With frmDepartamento.cbGrupo
            .DataSource = dt3
            .DisplayMember = dt3.Columns.Item(1).Caption
            .ValueMember = dt3.Columns.Item(0).Caption
        End With

        desconectar()

    End Sub



    Sub datosDepCBBlock()

        conectar()
        Dim dt2 As New DataTable

        cmd = New OdbcCommand("SELECT ID, Nombre FROM  tblock", con)
        dr = cmd.ExecuteReader
        'executing the command and assigning it to connection 

        dt2.Load(dr, LoadOption.OverwriteChanges)
        With frmDepartamento.cbBlock
            .DataSource = dt2
            .DisplayMember = dt2.Columns.Item(1).Caption
            .ValueMember = dt2.Columns.Item(0).Caption
        End With

        desconectar()

    End Sub

    Sub datosDepCBBlockL()

        conectar()
        Dim dt2 As New DataTable

        cmd = New OdbcCommand("SELECT ID, Nombre FROM  tblock", con)
        dr = cmd.ExecuteReader
        'executing the command and assigning it to connection 

        dt2.Load(dr, LoadOption.OverwriteChanges)
        With frmDepartamento.cbSelBlock
            .DataSource = dt2
            .DisplayMember = dt2.Columns.Item(1).Caption
            .ValueMember = dt2.Columns.Item(0).Caption
        End With
        ' listaDepartamentos(frmDepartamento.cbSelBlock.SelectedValue)
        desconectar()

    End Sub


    Function datosDepCBPersona()
        Dim contador As Integer
        Dim val As Integer = 0


        Dim i As Integer

        frmDepartamento.cbPersona.Items.Clear()
        conectar()
        cmd.Connection = con
        cmd.CommandText = "SELECT count(Nombre) FROM tpersonas"
        cmd.CommandType = CommandType.Text
        dr = cmd.ExecuteReader
        If dr.Read Then
            contador = dr.Item(0)
        End If
        dr.Close()

        Dim VectorDepartamento(contador, 1) As String
        cmd.Connection = con
        cmd.CommandText = "SELECT ID, Nombre, ApPaterno, ApMaterno FROM  tpersonas ORDER BY Nombre"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader

        If dr.HasRows Then
            While dr.Read()
                VectorDepartamento(i, 0) = dr("ID").ToString

                VectorDepartamento(i, 1) = dr("Nombre").ToString + " " + dr("ApPaterno").ToString + " " + dr("ApMaterno").ToString

                frmDepartamento.cbPersona.Items.Add(VectorDepartamento(i, 1))
                i += 1

            End While
        End If
        dr.Close()
        desconectar()

        Return VectorDepartamento



    End Function


    Function datosDepCBPersonaDueño()
        Dim contador As Integer
        Dim val As Integer = 0


        Dim i As Integer

        frmDepartamento.cbPersonaD.Items.Clear()
        conectar()
        cmd.Connection = con
        cmd.CommandText = "SELECT count(Nombre) FROM tpersonas"
        cmd.CommandType = CommandType.Text
        dr = cmd.ExecuteReader
        If dr.Read Then
            contador = dr.Item(0)
        End If
        dr.Close()

        Dim VectorDepartamento(contador, 1) As String
        cmd.Connection = con
        cmd.CommandText = "SELECT ID, Nombre, ApPaterno, ApMaterno FROM  tpersonas ORDER BY Nombre"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader

        If dr.HasRows Then
            While dr.Read()
                VectorDepartamento(i, 0) = dr("ID").ToString

                VectorDepartamento(i, 1) = dr("Nombre").ToString + " " + dr("ApPaterno").ToString + " " + dr("ApMaterno").ToString

                frmDepartamento.cbPersonaD.Items.Add(VectorDepartamento(i, 1))
                i += 1

            End While
        End If
        dr.Close()
        desconectar()

        Return VectorDepartamento



    End Function


    Sub ingresaDepartamento(ByVal vActual As String, ByVal vAnterior As String, ByVal vBlock As Integer, ByVal vPersona As Integer, ByVal vGrupo As Integer, ByVal vRol As String, ByVal vNic As String, ByVal vNicA As String, ByVal vTipo As Integer)

        conectar()
        cmd = New OdbcCommand("INSERT INTO tdepartamento (NumeroActual,NumeroAnterior,IDBlock,IDPersona ,IDGruposDeGastos,ROL, NIC, NICAgua,IDPropietario) Values('" + vActual + "','" + vAnterior + "','" & vBlock & "','" & vPersona & "','" & vGrupo & "','" + vRol + "','" + vNic + "','" + vNicA + "','" & vTipo & "')", con)

        If cmd.ExecuteNonQuery Then
            listaDepartamentos(vBlock)

        Else
            MessageBox.Show(msjNoIngresar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)

        End If

        desconectar()
    End Sub



    Dim dtDpto As New DataTable

    Sub listaDepartamentos(ByVal pID As Integer)

        conectar()
        dtDpto.Clear()

        cmd.Connection = con
        cmd.CommandText = "SELECT d.ID,d.NumeroActual,d.NumeroAnterior,p.Nombre,p.ApPaterno,p.ApMaterno,g.Nombre, d.ROL, d.NIC, d.NICAgua "
        cmd.CommandText += "FROM tgruposdegastos g , tdepartamento d INNER JOIN tpersonas p ON p.ID=d.IDPropietario "
        cmd.CommandText += "WHERE  d.IDBlock='" & pID & "' AND  d.IDGruposDeGastos = g.ID"
        cmd.CommandType = CommandType.Text
        da.SelectCommand = cmd
        da.Fill(dtDpto)

        'Çargamos el Datagridview
        With frmDepartamento.DGV_Departamentos
            .DataSource = dtDpto
            .AlternatingRowsDefaultCellStyle.BackColor = Color.AntiqueWhite
            .DefaultCellStyle.Font = New Font("Verdana", 10)
            .AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.DisplayedCells
            .AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.DisplayedCells
            .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize

            .Columns(0).HeaderText = "ID"
            .Columns(0).Visible = False
            .Columns(1).ReadOnly = True
            .Columns(1).HeaderText = "N° Actual"
            .Columns(2).HeaderText = "N° Anterior"
            .Columns(3).HeaderText = "Persona a cargo"
            .Columns(4).HeaderText = ""
            .Columns(5).HeaderText = ""
            .Columns(6).HeaderText = "Grupo de gastos"
            .Columns(7).HeaderText = "ROL"
            .Columns(8).HeaderText = "NIC"
            .Columns(9).HeaderText = "NIC Agua"
            '.Columns(10).HeaderText = "Propietario"
            .Refresh()

        End With

        desconectar()
    

    End Sub


   

End Module
